﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.Data.SqlClient;
using DataAccessLogic;
using System.Collections;
using System.Data;
using System.Web.Security;

namespace BusinessLogic
{
    public abstract class Table
    {
        #region Fields
        protected Guid _id;
        protected DateTime _dateAdded;
        protected DateTime _dateChanged;
        protected Guid _addedByID;
        protected Guid _changedByID;
        private DatabaseInteractionDescriptor _storedProcedures;
        #endregion        
        
        #region Methods
        protected Table() : this(Guid.Empty) {  }

        protected Table(Guid id)
        {
            SetInitialState();

            if (Guid.Empty != id)
            {                
                this._id = id;
                
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", id));
                this.GetByID(parms);
            }
        }

        private void SetInitialState()
        {
            _storedProcedures = TableInteractionDescription();            
        }

        public abstract DatabaseInteractionDescriptor TableInteractionDescription();

        public virtual void GetByID(List<SqlParameter> parms) 
        {
            string sproc = _storedProcedures.GetByIDStoredProcedure;

            if(!string.IsNullOrWhiteSpace(sproc))
            {
                DataTable dt = DataAccess.Get(sproc, parms);//BuildParameterList(SQLDoWhatEnum.Get, sproc));
                if (0 == dt.Rows.Count)
                {
                    _id = Guid.Empty;
                }
                foreach (DataRow row in dt.Rows)
                {
                    DataAccess.BindPropertyValuesFromDataRow(row, this);
                }                
            }
            else
            {
                throw new NotImplementedException("GetByIDStoredProcedure property has not yet been set.");
            }
        }

        public virtual void Create() { }
        
        public virtual void Create(List<SqlParameter> parms)
        {    
            string sproc = _storedProcedures.CreateStoredProcedure;

            if (!string.IsNullOrWhiteSpace(sproc))
            {
                object result = DataAccess.Create(sproc, "@ID", parms);

                if (null != result)
                {
                    this.ID = new Guid(result.ToString());
                }
            }
            else
            {
                throw new NotImplementedException("CreateStoredProcedure property has not yet been set.");
            }
        }        
        
        public virtual void Update(List<SqlParameter> parms)
        {            
            //so that the addedbyid doesnt get sent as the 
            //@ASPNETDBUSERID parameter to the stored procedure
            this.AddedByID = Guid.Empty;
            this.ChangedByID = new Guid(Membership.GetUser().ProviderUserKey.ToString());

            string sproc = _storedProcedures.UpdateStoredProcedure;

            if (!string.IsNullOrWhiteSpace(sproc))
            {
                DataAccess.Update(sproc, parms);//BuildParameterList(SQLDoWhatEnum.Update, sproc));
            }
            else
            {
                throw new NotImplementedException("UpdateStoredProcedure property has not yet been set.");
            }
        }

        public virtual void Delete()
        {
            Delete(null);
        }

        public virtual void Delete(List<SqlParameter> parms)
        {   
            //so that the @ASPNETDBUSERID parameter doesnt 
            //get sent to the stored procedure
            //this.AddedByID = Guid.Empty;
            //this.ChangedByID = Guid.Empty;

            string sproc = _storedProcedures.DeleteStoredProcedure;

            if (!string.IsNullOrWhiteSpace(sproc))
            {
                DataAccess.Delete(sproc, parms);//BuildParameterList(SQLDoWhatEnum.Delete, sproc));
            }
            else
            {
                throw new NotImplementedException("DeleteStoredProcedure property has not yet been set.");
            }
        }        


        //not checking this in, because i have no clue what kind of catastrophe this is.
        //it works and creates the params and gets the value correctly, however it duplicates params 
        //or is mixing up the values with ids or something. please help.
        private List<SqlParameter> BuildParameterList(SQLDoWhatEnum sqlDoWhat, string storedProcedure)
        {
            //what type of object am i?
            Type myType = this.GetType();

            //get all of the properties of this object
            IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());

            //create the parameter list to be returned
            List<SqlParameter> parms = new List<SqlParameter>();

            //loop through the properties on this object
            foreach (PropertyInfo prop in props)
            {
                //get the custom attributes on the current property
                object[] attrs = prop.GetCustomAttributes(true);
                
                //get the 
                foreach (object item in attrs)
                {
                    //if the attribute is my custom type
                    if (item is SQLParameterAttribute)
                    {
                        //cast as my custom attribute
                        SQLParameterAttribute a = (SQLParameterAttribute)item;
                        
                        if(a.SQLDoWhat == SQLDoWhatEnum.Delete)
                        {
                            a.StoredProcedure = storedProcedure; //just makes shit easier
                        }

                        if(a.IsClass && prop.GetGetMethod().ReturnType.IsClass)
                        {
                            //dive into this object and get any params
                            PropertyInfo[] nestedProps = prop.PropertyType.GetProperties();

                            List<SQLParameterAttribute> holdon = new List<SQLParameterAttribute>();

                            foreach (PropertyInfo prop2 in nestedProps)
                            {
                                //get the custom attributes on the current property
                                object[] attrs2 = prop2.GetCustomAttributes(true);
                
                                //get the 
                                foreach (object item2 in attrs2)
                                {
                                    //if the attribute is my custom type
                                    if (item2 is SQLParameterAttribute)
                                    {
                                        //cast as my custom attribute
                                        SQLParameterAttribute a2 = (SQLParameterAttribute)item2;

                                        if(!holdon.Contains(a2))
                                        {
                                            holdon.Add(a2);

                                            //see if the current sqldowhat is the same as the propertys sqldowhat
                                            //and make sure the attribute contains the parametername
                                            if ((sqlDoWhat == a2.SQLDoWhat) && (!string.IsNullOrWhiteSpace(a2.ParameterName)) && (a2.StoredProcedure == storedProcedure))
                                            {
                                                //get the value of the current property
                                                object propValue = prop2.GetValue(prop.GetValue(this, null), null);

                                                //create the parameter from the property
                                                SqlParameter p = new SqlParameter(a2.ParameterName, propValue);

                                                //if these parameters are for a create statement 
                                                //then lets mark the @ID parameter as output
                                                if (SQLDoWhatEnum.Create == sqlDoWhat && a2.ParameterName.Equals("@ID"))
                                                {
                                                    //send that shit out
                                                    p.Direction = ParameterDirection.Output;
                                                }

                                                IEnumerable<SqlParameter> tmp = parms.Where(t => t.Value == p.Value);

                                                if (tmp.Count() < 1)
                                                {
                                                    //add the property to the list being returned
                                                    parms.Add(p);
                                                }
                                            }
                                        }
                                    }                                    
                                }
                            }
                        }

                        //see if the current sqldowhat is the same as the propertys sqldowhat
                        //and make sure the attribute contains the parametername
                        if ((sqlDoWhat == a.SQLDoWhat) && (!string.IsNullOrWhiteSpace(a.ParameterName)) && (a.StoredProcedure == storedProcedure))
                        {
                            //get the value of the current property
                            object propValue = prop.GetValue(this, null);

                            //create the parameter from the property
                            SqlParameter p = new SqlParameter(a.ParameterName, propValue);

                            //if these parameters are for a create statement 
                            //then lets mark the @ID parameter as output
                            if (SQLDoWhatEnum.Create == sqlDoWhat && a.ParameterName.Equals("@ID"))
                            {
                                //send that shit out
                                p.Direction = ParameterDirection.Output;
                            }

                            if(!parms.Contains(p))
                            {
                                //add the property to the list being returned
                                parms.Add(p);
                            }
                        }
                    }
                }
            }

            //empty parameter to hold
            SqlParameter parm = null;

            //see if there is an addedbyid if so then lets add it
            if (Guid.Empty != this.AddedByID)
            {
                parm = new SqlParameter("@ASPNETDBUSERID", this.AddedByID);
            }
            //no addedbyid so maybe there is a changedbyid
            else if (Guid.Empty != this.ChangedByID)
            {
                parm = new SqlParameter("@ASPNETDBUSERID", this.ChangedByID);
            }

            //this is an add or update
            if (null != parm)
            {
                parms.Add(parm);
            }

            //return the list of parameters
            return parms;
        }
        #endregion

        #region Properties

        [SQLParameter(ParameterName = "@COUNTRYID", SQLDoWhat = SQLDoWhatEnum.Create, StoredProcedure = StoredProcedures.USP_ADDRESS_CREATE)]
        [SQLParameter(ParameterName = "@STATEID", SQLDoWhat = SQLDoWhatEnum.Create, StoredProcedure = StoredProcedures.USP_ADDRESS_CREATE)]
        [SQLParameter(ParameterName = "@ADDRESSTYPECODEID", SQLDoWhat = SQLDoWhatEnum.Create, StoredProcedure = StoredProcedures.USP_ADDRESS_CREATE)]
        [SQLParameter(ColumnName = "ID", ParameterName = "@ID", SQLDoWhat = SQLDoWhatEnum.Get, StoredProcedure = StoredProcedures.USP_CONSTITUENT_GETBYID)]
        [SQLParameter(ColumnName = "PHONETYPECODEID", SQLDoWhat = SQLDoWhatEnum.Get)]
        [SQLParameter(ColumnName = "ADDRESSTYPECODEID", SQLDoWhat = SQLDoWhatEnum.Get)]
        [SQLParameter(ColumnName = "EMAILADDRESSTYPECODEID", SQLDoWhat = SQLDoWhatEnum.Get)]
        [SQLParameter(ColumnName = "ID", ParameterName = "@ID", SQLDoWhat = SQLDoWhatEnum.Create, StoredProcedure = StoredProcedures.USP_CONSTITUENT_CREATE)]
        [SQLParameter(ColumnName = "ID", ParameterName = "@ID", SQLDoWhat = SQLDoWhatEnum.Update)]
        [SQLParameter(ColumnName = "ID", ParameterName = "@ID", SQLDoWhat = SQLDoWhatEnum.Delete)]
        public Guid ID
        {
            get
            {
                return _id;
            }
            set
            {
                _id = value;
            }
        }

        [SQLParameter(ColumnName = "DATECHANGED", SQLDoWhat = SQLDoWhatEnum.Get)]
        public DateTime DateChanged
        {
            get
            {
                return _dateChanged;
            }
            set
            {
                _dateChanged = value;
            }
        }

        [SQLParameter(ColumnName = "DATEADDED", SQLDoWhat = SQLDoWhatEnum.Get)]
        public DateTime DateAdded
        {
            get
            {
                return _dateAdded;
            }
            set
            {
                _dateAdded = value;
            }
        }

        [SQLParameter(ColumnName = "ADDEDBYID", SQLDoWhat = SQLDoWhatEnum.Get)]
        public Guid AddedByID
        {
            get
            {
                return _addedByID;
            }
            set
            {
                _addedByID = value;
            }
        }

        [SQLParameter(ColumnName = "CHANGEDBYID", SQLDoWhat = SQLDoWhatEnum.Get)]
        public Guid ChangedByID
        {
            get
            {
                return _changedByID;
            }
            set
            {
                _changedByID = value;
            }
        }
        #endregion
    }
}